package com.ryhx.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.WorkbookFactory;
@SuppressWarnings("all")
public class ExcelUtils {
	static Connection conn = ConnectionUtil.getConnection();
	static int total_max_count = 1500000;//总记录数
	static int oneSheet_max_count = 65536;//一个sheet最大记录数
	static int pageIndexMax = (total_max_count % oneSheet_max_count == 0) ? (total_max_count / oneSheet_max_count) : ((total_max_count / oneSheet_max_count) + 1);//最大页码
	static String fileName = "E:/students.xls";

	public static void main(String[] args) throws Exception {
		initFile();//初始化文件
		exportData();//执行导出
	}
	private static void initFile() throws IOException {
		File file = new File(fileName);
		if(!file.exists()){
			file.createNewFile();
		}
		// 1.创建Excel工作薄对象
		HSSFWorkbook wb = new HSSFWorkbook();
		// 2.创建Excel工作表对象
		HSSFSheet sheet = null;
		for(int i = 0 ; i < pageIndexMax ; i++){
			sheet = wb.createSheet("sheet" + i);
			wb.setSheetName(i,"sheet" + i);
		}
		// 最后一步，将文件存到指定位置
		try {
			FileOutputStream fout = new FileOutputStream(fileName);
			wb.write(fout);
			fout.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	private static void exportData() throws Exception {
		long start = System.currentTimeMillis();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		for(int sheetIndex = 22 ; sheetIndex < pageIndexMax ; sheetIndex++){
			long l1 = System.currentTimeMillis();
			String sql = "select id,title from test "+ "limit " + sheetIndex * oneSheet_max_count + "," + oneSheet_max_count;
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery(sql);
			exportToExcel(rs,sheetIndex);
			rs.close();
			long l2 = System.currentTimeMillis();
			System.out.println(sql + " 执行导出时间为：" + (l2 -l1) + "毫秒");
		}
		stmt.close();
		ConnectionUtil.releaseConn();
		long end = System.currentTimeMillis();
		System.out.println((end - start) + "毫秒");
	}
	public static void exportToExcel(ResultSet rs,int sheetIndex) throws Exception {
		// 1.创建Excel工作薄对象
		HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(new File(fileName)));
		// 2.创建Excel工作表对象
		HSSFSheet sheet = null;
		// 3.创建Excel工作表的行
		HSSFRow row = null;
		// 4.创建单元格样式
		CellStyle cellStyle = wb.createCellStyle();
		sheet = wb.getSheetAt(sheetIndex);
		int i = 0;
		while (rs.next()) {
			row = sheet.createRow(i);
			i++;
			row.createCell(0).setCellStyle(cellStyle);
			row.createCell(0).setCellValue(rs.getInt(1));
			row.createCell(1).setCellStyle(cellStyle);
			row.createCell(1).setCellValue(rs.getInt(2));
		}
		// 最后一步，将文件存到指定位置
		try {
			FileOutputStream fout = new FileOutputStream(fileName);
			wb.write(fout);
			fout.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private static void a() throws Exception {
		String sql = "select id,title from test";
		PreparedStatement stmt;
		long start = System.currentTimeMillis();
		try {
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			CreateExcel(rs);
			rs.close();
			ConnectionUtil.releaseConn();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		long end = System.currentTimeMillis();
		System.out.println((end - start) + "毫秒");
	}

	/**
	 * 创建Excel，并写入内容
	 * @throws Exception 
	 */
	public static void CreateExcel(ResultSet rs) throws Exception {

		// 1.创建Excel工作薄对象
		HSSFWorkbook wb = new HSSFWorkbook();
		// 2.创建Excel工作表对象
		HSSFSheet sheet = null;
		// 3.创建Excel工作表的行
		HSSFRow row = null;
//		HSSFRow row = sheet.createRow(6);
		// 4.创建单元格样式
		CellStyle cellStyle = wb.createCellStyle();
		// 设置这些样式
		cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		int i = 0;
		int sheetSize = 0;
		while (rs.next()) {
			if(i % 65535 == 0){
				sheet = wb.createSheet("sheet" + sheetSize);
				wb.setSheetName(sheetSize,sheetSize + "张工作表");
				sheetSize ++;
				i = 0;
			}
			row = sheet.createRow(i);
			i++;
//			System.out.println(rs.getInt(1) + "," + rs.getString(2));
			row.createCell(0).setCellStyle(cellStyle);
			row.createCell(0).setCellValue(rs.getInt(1));
			row.createCell(1).setCellStyle(cellStyle);
			row.createCell(1).setCellValue(rs.getInt(2));
		}
		// 5.创建Excel工作表指定行的单元格
//		row.createCell(0).setCellStyle(cellStyle);
		// 6.设置Excel工作表的值
//		row.createCell(0).setCellValue("aaaa");

//		row.createCell(1).setCellStyle(cellStyle);
//		row.createCell(1).setCellValue("bbbb");

		// 设置sheet名称和单元格内容
//		wb.setSheetName(0, "第一张工作表");
		// 设置单元格内容 cell.setCellValue("单元格内容");

		// 最后一步，将文件存到指定位置
		try {
			FileOutputStream fout = new FileOutputStream(fileName);
			wb.write(fout);
			fout.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
